#!/usr/bin/env python
"""
This module Comprises of the data layer of the SSO app. All the data base
interaction such as firing SQL stmts is done in this module.

Data objects also reside in this module
"""
import time
import sys
import inspect
import os.path

#Import Iservices specific database classes
from db import DBConnectionPool
from db import DBConnection
from db import StartDBTransaction

#Import Iservices specific Data objects
from dataobject import *

#Import Iservices specific Exception classes
from exception import IservicesException
from exception import IservicesSysException

# Error codes and constants
from errcode import *
from constants import *
from properties import Properties

# Required for logging
import logging
from logutil import LogUtil

class IssoHelpers:
    
    #SQL query to get the cookie
    sFetchCookieSql = """SELECT PRINCIPAL_ID, MODIFICATION_TIME, STATE_ID,
            COOKIE_ID FROM COOKIE WHERE ENC_COOKIE_NAME= "%s" AND TYPE_FLAG=%d;
            """

    #SQL query to get the principal
    sFetchPrincipalSql = """SELECT PRINCIPAL.PRINCIPAL_ID, PRINCIPAL.IBROKER_ID,
            PRINCIPAL.STATE_ID, PRINCIPAL_METADATA.ENC_PASSWD FROM  
            PRINCIPAL_METADATA INNER JOIN PRINCIPAL ON 
            PRINCIPAL_METADATA.PRINCIPAL_ID=PRINCIPAL.PRINCIPAL_ID AND
            PRINCIPAL.PRINCIPAL_ID=(SELECT SYNONYM.PRINCIPAL_ID FROM SYNONYM WHERE
            SYNONYM="%s");"""

    #SQL query to get the Ibroker
    #TODO: QUERY TO BE CHANGED TO GET IBROKER ID AND DO NOT RETURN NONE FOR LACK
    #OF DATA
    sFetchIbrokerSql = """SELECT url, item_name, IBROKER_ID FROM 
        IBROKER_DATA WHERE IBROKER_ID=(SELECT IBROKER_ID FROM IBROKER WHERE 
        SERVER_NAME="%s" AND STATE_ID=%d);"""

    #SQL query to get the principal's personalized greeting    
    sFetchGreetingSql = """SELECT
        PRINCIPAL_METADATA.ENC_PERSONALIZED_GREETING FROM
        PRINCIPAL_METADATA INNER JOIN PRINCIPAL ON
        PRINCIPAL_METADATA.PRINCIPAL_ID=PRINCIPAL.PRINCIPAL_ID AND
        PRINCIPAL.PRINCIPAL_ID=(SELECT SYNONYM.PRINCIPAL_ID FROM SYNONYM WHERE
        SYNONYM="%s") AND PRINCIPAL.STATE_ID=1"""
 
    #SQL query to update the Cookie modification time
    sUpdateCookieSql = """UPDATE COOKIE SET MODIFICATION_TIME="%s"
        WHERE COOKIE_ID=%d"""

    #SQL query to expire cookie of type requested
    #sDeleteCookieSql = """UPDATE COOKIE SET STATE_ID = """ + str(COOKIE_STATE_INACTIVE) + """ WHERE ENC_COOKIE_NAME="%s" AND TYPE_FLAG = %d""" 
    sDeleteCookieSql = """DELETE FROM COOKIE WHERE ENC_COOKIE_NAME="%s" AND TYPE_FLAG = %d""" 
	
    #SQL query to create the cookie in the database for the principal specified
    sCreateCookieSql = """INSERT INTO COOKIE (PRINCIPAL_ID,
        ENC_COOKIE_NAME, TYPE_FLAG, CREATION_TIME, MODIFICATION_TIME, STATE_ID)
        VALUES ((SELECT PRINCIPAL_ID FROM SYNONYM WHERE SYNONYM="%s"), "%s",
        %d, "%s", "%s", %d) """

    """
        Function to get cookie object, of the cookie value received in request
    """
    def fetchCookie(sCookie, iType):
        
        LogUtil.log(logging.INFO, 'fetchCookie(): Entry')
        #Raise exception if any arg. is None
        if(sCookie == None or iType == None):
            LogUtil.log(logging.WARN, 'fetchCookie(): sCookie=' + str(sCookie) +
                        ', iType=' + str(iType))
            LogUtil.log(logging.WARN, 'fetchCookie(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        #Create SQL query to get the Cookie
        sSql = IssoHelpers.sFetchCookieSql %(sCookie,iType)
        LogUtil.log(logging.INFO, 'fetchCookie(): SQL=' + str(sSql))
        
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        
        # Exceute the SQL to get the result
        try:
            oDbConnection.execute(sSql)
            tRecord = oDbConnection.fetchAll()
        except:
            LogUtil.log(logging.WARN, 'fetchCookie(): Exception: ' +
                        'QUERY_FAILED')
            raise IservicesSysException(QUERY_FAILED,
                                        dUtilErrorCodeMap[QUERY_FAILED])
        
        #get fields from result to populate the class and return the class obj.
        if (tRecord == None or tRecord==()):
            LogUtil.log(logging.INFO, 'fetchCookie(): Exit. Return=None')    
            return None
        
        if (tRecord[0][2] == COOKIE_STATE_ACTIVE):
            LogUtil.log(logging.DEBUG, "fetchCookie(): Modification time=" +
                        str(tRecord[0][1]))
            LogUtil.log(logging.INFO, 'fetchCookie(): Exit. Return=Cookie object') 
            return Cookie(tRecord[0][2], iType, tRecord[0][0], tRecord[0][1],
                          tRecord[0][3])
        else:
             #Throw exception if Cookie is not active
            LogUtil.log(logging.INFO, 'fetchCookie(): Exception: SESSION_INVALID') 
            raise IservicesSysException(SESSION_INVALID,
                                        dUtilErrorCodeMap[SESSION_INVALID])

    """
        Function to get cookie object, of the cookie value received in request
    """
    def fetchPrincipal(sGinu):
        
        LogUtil.log(logging.INFO, 'fetchPrincipal(): Entry')
        #Raise exception if any arg. is None
        if(sGinu == None):
            LogUtil.log(logging.WARN, 'fetchPrincipal(): sGinu=' + str(sGinu))
            LogUtil.log(logging.WARN, 'fetchPrincipal(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        sSql = IssoHelpers.sFetchPrincipalSql %sGinu
        LogUtil.log(logging.INFO, 'fetchPrincipal: SQL=' + str(sSql))
        
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        
        # Exceute the SQL to get the result
        try:
            oDbConnection.execute(sSql)
            tRecord = oDbConnection.fetchAll()
        except:
            LogUtil.log(logging.WARN, 'fetchPrincipal(): Raising exception: ' +
                        'QUERY_FAILED')
            raise IservicesSysException(QUERY_FAILED,
                                        dUtilErrorCodeMap[QUERY_FAILED])
        
        #get fields from result to populate the class and return the class obj.
        if (tRecord == None or tRecord==()):
            LogUtil.log(logging.INFO, 'fetchPrincipal(): Exit. Return=None')
            return None
        else:
            #Raise exc if Principal is not active
            if (tRecord[0][2] == PRINCIPAL_STATE_ACTIVE):
                LogUtil.log(logging.INFO, 'fetchPrincipal(): Exit. Return=Principal '+ 
                            'object')
                return Principal(tRecord[0][0], tRecord[0][1],
                                 PRINCIPAL_STATE_ACTIVE, tRecord[0][3])
            else:
                LogUtil.log(logging.INFO, 'fetchPrincipal(): Exception: ACCOUNT_INACTIVE') 
                raise IservicesException(ACCOUNT_INACTIVE,
                                        dUtilErrorCodeMap[ACCOUNT_INACTIVE])
    
    """
        Function to retrieve IBroker object, to which the principal belongs
    """
    def fetchIBroker(sBrokerName):
        LogUtil.log(logging.INFO, 'fetchIBroker(): Entry')
        #Raise exception if any arg. is None
        if(sBrokerName == None):
            LogUtil.log(logging.WARN, 'fetchIBroker(): sBrokerName=' +
                        str(sBrokerName))
            LogUtil.log(logging.WARN, 'fetchIBroker(): Exception: '
                        + 'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        sSql = IssoHelpers.sFetchIbrokerSql %(sBrokerName,IBROKER_STATE_ACTIVE)
        LogUtil.log(logging.INFO, 'fetchIBroker(): SQL=' + str(sSql))
      
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        
        # Exceute the SQL to get the result
        try:
            oDbConnection.execute(sSql)
            tRecord = oDbConnection.fetchAll()
        except:
            LogUtil.log(logging.WARN, 'fetchIBroker(): Exception: ' +
                        'QUERY_FAILED')
            raise IservicesSysException(QUERY_FAILED,
                                        dUtilErrorCodeMap[QUERY_FAILED])
        
        #get fields from result to populate the class and return the class obj.
        if (tRecord == None or tRecord==()):
            LogUtil.log(logging.INFO, 'fetchIBroker(): Exit. Return=None') 
            return None
        
        # Create dict of the links and text
        odData ={}
        for tItem in tRecord :
            data = {tItem[1] : tItem[0]}
            odData.update(data)
        
        LogUtil.log(logging.DEBUG, 'fetchIBroker(): Broker Data:' + str(odData))
        LogUtil.log(logging.INFO, 'fetchIBroker(): Exit. return=Principal Object')
        
        return IBroker(IBROKER_STATE_ACTIVE,tRecord[0][2], odData)
    
    """
        Function to retrieve personalized greeting of the principal
    """
    def getPersonalizedGreeting(sGinu):
        
        LogUtil.log(logging.INFO, 'getPersonalizedGreeting(): Entry')
        #Raise exception if any arg. is None
        if(sGinu == None):
            LogUtil.log(logging.WARN, 'getPersonalizedGreeting(): sGinu=' +
                        str(sGinu))
            LogUtil.log(logging.WARN, 'getPersonalizedGreeting(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        #Query to retrieve greeting of synonym's principal, if state is active
        sSql = IssoHelpers.sFetchGreetingSql  %sGinu
        LogUtil.log(logging.INFO, 'getPersonalizedGreeting(): SQL=' + str(sSql))
        
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        
        # Exceute the SQL to get the result
        try:
            oDbConnection.execute(sSql)
            tRecord = oDbConnection.fetchAll()
        except:
            LogUtil.log(logging.WARN, 'getPersonalizedGreeting(): Exception: ' +
                        'QUERY_FAILED')
            raise IservicesSysException(QUERY_FAILED,
                                        dUtilErrorCodeMap[QUERY_FAILED])
        
        if (tRecord == None or tRecord ==()):
            LogUtil.log(logging.WARN, 'getPersonalizedGreeting(): Exit. Return=None') 
            return None
        else:
            LogUtil.log(logging.WARN, 'getPersonalizedGreeting(): Exit. Return='
                        + str(tRecord[0][0])) 
            return tRecord[0][0]

    """
        Function to update Cookie
    """
    def updateCookie(iId):
        
        LogUtil.log(logging.INFO, 'updateCookie(): Entry')
        #Raise exception if any arg. is None
        if(iId == None):
            LogUtil.log(logging.WARN, 'updateCookie(): iId=' + str(iId))
            LogUtil.log(logging.WARN, 'updateCookie(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        #Get system current time
        sModTime = time.strftime(DEFAULT_TIME_FORMAT)
        LogUtil.log(logging.DEBUG, 'updateCookie(): Modification Time='
                    + str(sModTime))
        
        sSql = IssoHelpers.sUpdateCookieSql %(sModTime,iId)
        LogUtil.log(logging.INFO, 'updateCookie(): SQL=' + str(sSql))
       
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        oTran = StartDBTransaction(oDbConnection)
        oDbConnection.execute(sSql)
        oTran.setSuccess()
        LogUtil.log(logging.INFO, 'updateCookie(): Exit. Success')
    
    """
        Function to delete cookie in the database
    """
    def deleteCookie(sEncCookie, iType):
        
        LogUtil.log(logging.INFO, 'deleteCookie(): Entry')
        #Raise exception if any arg. is None
        if(sEncCookie == None and iType == None ):
            LogUtil.log(logging.WARN, 'deleteCookie(): sEncCookie=' +
                        str(sCookie) + ', iType=' + str(iType))
            LogUtil.log(logging.WARN, 'deleteCookie(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        #Sql query to expire cookie
        sSql = IssoHelpers.sDeleteCookieSql %(sEncCookie, iType)
        LogUtil.log(logging.INFO, 'deleteCookie(): SQL=' + str(sSql))
        
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        oTran = StartDBTransaction(oDbConnection)
        oDbConnection.execute(sSql)
        oTran.setSuccess()
        LogUtil.log(logging.INFO, 'deleteCookie(): Exit. Success')
        
    """
        Function to delete cookie in the database
    """
    def createCookie(sEncCookie, sGINU, iType):
        #Raise exception if any arg. is None
        if(sEncCookie == None or sGINU == None or iType == None ):
            LogUtil.log(logging.WARN, 'createCookie(): sEncCookie='
                        + str(sEncCookie) + ', sGINU=' + str(sGINU) + ', iType='
                        + str(iType))
            LogUtil.log(logging.WARN, 'createCookie(): Exception: ' +
                        'INSUFFICENT_DATA')
            raise IservicesSysException(INSUFFICENT_DATA,
                                        dUtilErrorCodeMap[INSUFFICENT_DATA])
        
        sCurrentTime = time.strftime(DEFAULT_TIME_FORMAT)
        LogUtil.log(logging.DEBUG, 'createCookie(): Current Time='
                    + str(sCurrentTime))
        
        #Sql query to create cookie
        sSql = IssoHelpers.sCreateCookieSql %(sGINU, sEncCookie, iType,
                                             sCurrentTime, sCurrentTime,
                                             COOKIE_STATE_ACTIVE)
        LogUtil.log(logging.INFO, 'createCookie(): SQL=' + str(sSql))
        
        #Get database connection from singleton class DBPool
        oDbConnection = IssoHelpers.getDBConn()
        oTran = StartDBTransaction(oDbConnection)
        oDbConnection.execute(sSql)
        oDbConnection.commit()
        oTran.setSuccess()
        LogUtil.log(logging.INFO, 'createCookie(): Exit. Success')

    
    """
        Function to get database connection from DB pool    
    """
    def getDBConn():
        LogUtil.log(logging.INFO, 'getDBConn: Entry')
        
        #get absolute file path
        #sPath = inspect.getfile(sys._getframe(0))
        sPath=__file__
        sPath = os.path.dirname(sPath) + os.sep + "db.properties"
        
        LogUtil.log(logging.DEBUG, 'DB property file=' + sPath)
        
        oProp = Properties()
        oProp.load(open(sPath))
        
        try:
            oDbPool = DBConnectionPool(oProp['host'], oProp['user_name'],
            oProp['password'], oProp['database'], oProp['maxConnection'], 5,
            oProp['waitTime']).getTheInstance()
            oDbConnection = oDbPool.getConnection()
        except:
            LogUtil.log(logging.ERROR, 'DB Exception=' + str(sys.exc_info())) 
            raise IservicesException(NO_DATABASE_CONNECTION,
                                    dUtilErrorCodeMap[NO_DATABASE_CONNECTION])
        
        LogUtil.log(logging.INFO, 'getDBConn() Exit. returning DB conn obj') 
        return oDbConnection
    
    
 
    #MAKE ALL THE FUNCTIONS STATIC
    getPersonalizedGreeting = staticmethod(getPersonalizedGreeting)
    fetchCookie = staticmethod(fetchCookie)
    fetchIBroker = staticmethod(fetchIBroker)
    fetchPrincipal = staticmethod(fetchPrincipal)
    updateCookie = staticmethod(updateCookie)
    deleteCookie = staticmethod(deleteCookie)
    createCookie = staticmethod(createCookie)
    getDBConn = staticmethod(getDBConn)
